#encoding: utf-8
load 'tutem_utils.rb'

def totem_friend_uids
  return [3190,426,1806,2030,3413,2123,2027,4038,2992,425,653,1673,4220,1915,47,2914,2039,3035,3803,2958,440,2994,449,2930,2865,2931]
end

def review_group_members

  uids = []
  fn = 'group.json'
  data = JSON.parse IO.readlines(fn)[0]
  members = data['group_chat']['member_list']
  lines = []
  group_time = 1639981576 #群创建时间
  tfuids = totem_friend_uids
  members.each do |member|
    if !member['unionid'].nil?
      line = {}
      name = member['name']
      unionid = member['unionid']
      join_time = member['join_time']
      join_at = Time.at(join_time)
      line.store 'name',name
      line.store 'uionid', unionid
      line.store 'join_at', "'#{join_at}"

      line.store 'login_at','not login'
      line.store 'uid', 0
      line.store 'friendly', false
      sql = "select uid, from_unixtime(add_time) as login_at from tutem_mall.eb_wechat_user where unionid = '#{unionid}'"
      res = queryRds sql
      res.each do |r|
        line.store 'uid', r['uid']
        line.store 'friendly', true if tfuids.include? r['uid']
        line.store 'login_at', "'#{r['login_at']}"
      end

      line.store 'checkin', 0
      sql = "SELECT count(*) as checkin FROM tutem_mall.eb_user_bill where title like '%L211226-B%' and uid = #{line['uid']};"
      res = queryRds sql
      res.each do |r|
        line.store 'checkin', r['checkin']
      end

      uids += [line['uid']]

      line.store 'orders_before', 0
      line.store 'paid_before', 0.0
      sql = "select count(*) as order_count, sum(pay_price) as total_paid from tutem_mall.eb_store_order where paid = 1 and pay_time < #{group_time} and uid in (select uid from tutem_mall.eb_wechat_user where unionid = '#{unionid}')"
      res = queryRds sql
      res.each do |r|
        line.store 'orders_before', r['order_count']
        r['total_paid'] = 0.0 if r['total_paid'].nil?
        line.store 'paid_before', r['total_paid']
      end

      line.store 'orders_after', 0
      line.store 'paid_after', 0.0
      sql = "select count(*) as order_count, sum(pay_price) as total_paid from tutem_mall.eb_store_order where paid = 1 and pay_time >= #{group_time} and uid in (select uid from tutem_mall.eb_wechat_user where unionid = '#{unionid}')"
      res = queryRds sql
      res.each do |r|
        line.store 'orders_after', r['order_count']
        r['total_paid'] = 0.0 if r['total_paid'].nil?
        line.store 'paid_after', r['total_paid']
      end

      lines += [line]
    end
  end
  headers = ['name','join_at','login_at','uid','friendly','orders_before','paid_before','orders_after','paid_after', 'checkin']
  names = headers
  fn = "group_review_#{group_time}.xls"
  save_to_excel headers,names,lines,fn

  return uids

end

uids = review_group_members
remained_uids = totem_friend_uids - uids
ap remained_uids
